const Database = require('better-sqlite3')
const db = new Database('data.db')
// 红牌产生时的历史赔率
db.prepare(`
    CREATE TABLE IF NOT EXISTS games (
        id INTEGER PRIMARY KEY,
        league TEXT,
        home TEXT,
        away TEXT,
        score TEXT,
        matchId TEXT,
        redScore TEXT,
        time TEXT,
        market TEXT, -- 以 JSON 字符串形式存储
        ps_home TEXT,
        ps_away TEXT,
        homeEn TEXT,
        awayEn TEXT
    )
`).run()

// 创建 match_list 表 ，产生红牌的比赛列表
db.prepare(`
    CREATE TABLE IF NOT EXISTS match_list (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        time TEXT,
        matchId TEXT,
        matchName TEXT,
        league TEXT
    )
`).run()

// 修改表结构

function columnExists(db, tableName, columnName) {
    const stmt = db.prepare(`PRAGMA table_info(${tableName})`);
    const columns = stmt.all();
    if (!columns.some(col => col.name === columnName)) {
        db.prepare(`ALTER TABLE ${tableName} ADD COLUMN ${columnName} TEXT`).run();
    }
}

columnExists(db, 'games', 'ps_home')
columnExists(db, 'games', 'ps_away')
columnExists(db, 'games', 'homeEn')
columnExists(db, 'games', 'awayEn')
columnExists(db, 'games', 'orderData')
columnExists(db, 'games', 'updateTimeHG')
columnExists(db, 'games', 'updateTimePS')
columnExists(db, 'games', 'updateSite')
columnExists(db, 'games', 'psOrderData')
columnExists(db, 'games', 'psId')


// 存储赔率数据
exports.save = async function (game) {
    const stmt = db.prepare(`
        INSERT OR REPLACE INTO games (id, league, home, away, score, matchId, redScore, time, market,ps_home, ps_away,homeEn,awayEn,orderData,updateTimeHG,updateTimePS,updateSite,psOrderData,psId)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `)
    stmt.run(
        game.id,
        game.league,
        game.home,
        game.away,
        game.score,
        game.matchId,
        game.redScore,
        game.time,
        JSON.stringify(game.market),
        game.ps_home,
        game.ps_away,
        game.homeEn,
        game.awayEn,
        game.orderData,
        game.updateTimeHG,
        game.updateTimePS,
        game.updateSite,
        game.psOrderData,
        game.psId
    )
    // global.log('已写入数据库，id =', game.id)
}

// 根据matchId 查询赔率历史记录
exports.getByMatchId = function (matchId) {
    const stmt = db.prepare(`SELECT * FROM games WHERE matchId = ?`)
    // 执行查询
    const rows = stmt.all(matchId)
    // const stmt1 = db.prepare(`SELECT * FROM games`)
    // // 执行查询
    // const row1 = stmt1.all()

    if (rows) {
        // market 字段 JSON.parse 后使用
        rows.forEach(row => {
            row.market = JSON.parse(row.market)
        })
        return rows;
    } else {
        console.log('❌ 未找到 matchId =', matchId)
    }
    return false
}

exports.createRedHistory = function (matchId, matchName, league) {
    let rows = exports.getRedHistoryByMatchId(matchId);
    if (rows && rows.length > 0) {
        global.log('已存在红牌历史比赛，matchId =', matchId)
        return;
    }
    const stmt = db.prepare(`
        INSERT INTO match_list (time, matchId, matchName, league)
        VALUES (?, ?, ?, ?)
    `)
    const timeStr = Date.now();
    stmt.run(timeStr, matchId, matchName, league)
    global.log('已插入红牌历史比赛')
}

exports.getRedHistory = function () {
    const stmt = db.prepare(`SELECT * FROM match_list order by id desc limit 100`)
    // 执行查询
    const rows = stmt.all()
    if (rows) {
        return rows;
    } else {
        console.log('❌ 未找到红牌历史记录')
    }
    return false;
}

exports.getRedHistoryByMatchId = function (matchId) {
    const stmt = db.prepare(`SELECT * FROM match_list where matchId = ? order by id desc`)
    // 执行查询
    const rows = stmt.all(matchId)
    if (rows) {
        return rows;
    }
    console.log('❌ 未找到红牌历史记录')
    return false;
}


exports.delRedHistory = function (id) {
    const stmt = db.prepare(`DELETE FROM match_list WHERE id = ?`)
    // 执行删除
    const result = stmt.run(id)
    if (result.changes > 0) {
        global.log('已删除红牌历史记录，id =', id)
        return true;
    } else {
        console.log('❌ 未找到红牌历史记录，id =', id)
        return false;
    }
}